package com.serviceprovider.business.module.sp.payee.services.dao.impl;

//~--- non-JDK imports --------------------------------------------------------

import com.serviceprovider.business.module.admin.role.model.Role;
import com.serviceprovider.business.module.admin.role.services.dao.interfaces.IRoleService;
import com.serviceprovider.business.module.sp.payee.model.Payee;
import com.serviceprovider.business.module.sp.payee.services.dao.interfaces.IPayeeService;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Service;

//~--- JDK imports ------------------------------------------------------------

import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author mrduongnv@gmail.com
 *
 */
@Service("payeeService")
public class PayeeServiceDaoImpl extends JdbcDaoSupport implements IPayeeService {
    private static final String DELETE_FROM_USERS        = "delete from adm_users ";                  // $NON-NLS-1$
    private static final String F_DELETED                = "deleted";                                 // $NON-NLS-1$
    private static final String F_EMAIL                  = "email";                                   // $NON-NLS-1$
    private static final String F_EMAILGUID              = "emailguid";                               // $NON-NLS-1$
    private static final String F_ENABLED                = "enabled";                                 // $NON-NLS-1$
    private static final String F_ID                     = "id";                                      // $NON-NLS-1$
    private static final String F_LEVEL_1_USERID         = "Level_1_UserID";                          // $NON-NLS-1$
    private static final String F_PASSWORD               = "password";                                // $NON-NLS-1$
    private static final String F_ROLE                   = "role";                                    // $NON-NLS-1$
    private static final String F_SHOW_GUI               = "show_gui";                                // $NON-NLS-1$
    private static final String SELECT_FROM_USERS        = new StringBuilder("select ")               // $NON-NLS-1$
        .append(F_ID).append(", ")                                                                    // $NON-NLS-1$
        .append(F_EMAIL).append(", ")                                                                 // $NON-NLS-1$
        .append(F_EMAILGUID).append(", ")                                                             // $NON-NLS-1$
        .append(F_PASSWORD).append(", ")                                                              // $NON-NLS-1$
        .append(F_ENABLED).append(", ")                                                               // $NON-NLS-1$
        .append(F_DELETED).append(", ")                                                               // $NON-NLS-1$
        .append(F_SHOW_GUI).append(", ")                                                              // $NON-NLS-1$
        .append(F_LEVEL_1_USERID).append(", ")                                                        // $NON-NLS-1$
        .append(F_ROLE).append(" from adm_users ").toString();                                        // $NON-NLS-1$
    private static final String SELECT_BY_ROLE           = new StringBuilder("select * from user where role=").toString();
    private static final String INSERT_INTO_USERS        = new StringBuilder("insert into user (")    // $NON-NLS-1$
        .append(F_EMAIL).append(",")                                                                  // $NON-NLS-1$
        .append(F_EMAILGUID).append(",")                                                              // $NON-NLS-1$
        .append(F_PASSWORD).append(",")                                                               // $NON-NLS-1$
        .append(F_ENABLED).append(",")                                                                // $NON-NLS-1$
        .append(F_ROLE).append(",").append(F_LEVEL_1_USERID).append(") values (?,?,?,?,?,?)")         // $NON-NLS-1$
        .toString();
    private static final String UPDATE_USERS             = new StringBuilder("update user set ")      // $NON-NLS-1$
        .append(F_EMAIL).append(" = ?, ")                                                             // $NON-NLS-1$
        .append(F_EMAILGUID).append(" = ?, ")                                                         // $NON-NLS-1$
        .append(F_ENABLED).append(" = ?, ")                                                           // $NON-NLS-1$
        .append(F_ROLE).append(" = ? where ").toString();                                             // $NON-NLS-1$
    private static final String UPDATE_PAYEE_ACTIVE_FLAG = new StringBuilder("update user set ").append(
                                                               F_ENABLED).append("=? , ").append(F_DELETED).append(
                                                               "=? ,").append(F_SHOW_GUI).append("=? ").append(
                                                               " where ").toString();
    private static final String DELETE_USERS_EMAILGUID = new StringBuilder("update user set ")    // $NON-NLS-1$
        .append(F_EMAILGUID).append(" = null where ")                                             // $NON-NLS-1$
        .append(F_ID).append(" = ?").toString();                                                  // $NON-NLS-1$
    @Autowired
    private IRoleService        roleService;

    @Autowired
    public PayeeServiceDaoImpl(@Qualifier("jdbcTemplate") JdbcTemplate jdbcTemplate) {
        setJdbcTemplate(jdbcTemplate);
    }

    /*
     * (non-Javadoc)
     *
     * @see springjdbc.example.service.UserService#getById(int)
     */
    public Payee getById(int id) {
        RowMapper mapper = new RowMapper() {
            public Payee mapRow(ResultSet rs, int rowNum) throws SQLException {
                Payee payee = new Payee();

                payee.setId(rs.getInt(F_ID));
                payee.setEmail(rs.getString(F_EMAIL));
                payee.setAlternateEmail(rs.getString(F_EMAILGUID));
                payee.setEnabled(rs.getBoolean(F_ENABLED));
                payee.setDeleted(rs.getBoolean(F_DELETED));
                payee.setShowGui(rs.getBoolean(F_SHOW_GUI));
                payee.setLevel1UserID(rs.getInt(F_LEVEL_1_USERID));
                payee.setPassword(rs.getString(F_PASSWORD));
                payee.setRole(roleService.getRoleById(rs.getInt(F_ROLE)));

                return payee;
            }
        };
        final String sql = new StringBuilder(SELECT_FROM_USERS).append("where ").append(F_ID).append(" = ?").toString();    // $NON-NLS-1$ //$NON-NLS-2$

        try {
            return (Payee) getJdbcTemplate().queryForObject(sql, new Object[] { id }, mapper);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    /*
     * (non-Javadoc)
     *
     * @see springjdbc.example.service.UserService#findAll()
     */
    public List<Payee> findAll() {
        List        list   = getJdbcTemplate().queryForList(SELECT_FROM_USERS);
        List<Payee> payees = new ArrayList<Payee>();
        Map         row    = null;
        Payee       aux    = null;

        for (Object obj : list) {
            aux = new Payee();
            row = (Map) obj;
            aux.setId(((Long) row.get(F_ID)).intValue());
            aux.setEmail((String) row.get(F_EMAIL));
            aux.setAlternateEmail((String) row.get(F_EMAILGUID));
            aux.setEnabled(new Integer(1).equals(row.get(F_ENABLED)));
            aux.setDeleted(new Integer(1).equals(row.get(F_DELETED)));
            aux.setShowGui(new Integer(1).equals(row.get(F_SHOW_GUI)));

            if (row.get(F_LEVEL_1_USERID) != null) {
                aux.setLevel1UserID((Integer) row.get(F_LEVEL_1_USERID));
            }

            aux.setPassword((String) row.get(F_PASSWORD));
            aux.setRole(roleService.getRoleById(((Long) row.get(F_ROLE)).intValue()));
            payees.add(aux);
        }

        return payees;
    }

    public List<Payee> findAllForSP(int sp_id) {
        List list = getJdbcTemplate().queryForList(SELECT_FROM_USERS + " Where Level_1_UserID=?",
                        new Object[] { sp_id });
        List<Payee> payees = new ArrayList<Payee>();
        Map         row    = null;
        Payee       aux    = null;

        for (Object obj : list) {
            aux = new Payee();
            row = (Map) obj;
            aux.setId(((Long) row.get(F_ID)).intValue());
            aux.setEmail((String) row.get(F_EMAIL));
            aux.setAlternateEmail((String) row.get(F_EMAILGUID));
            aux.setEnabled(new Integer(1).equals(row.get(F_ENABLED)));
            aux.setDeleted(new Integer(1).equals(row.get(F_DELETED)));
            aux.setShowGui(new Integer(1).equals(row.get(F_SHOW_GUI)));

            if (row.get(F_LEVEL_1_USERID) != null) {
                aux.setLevel1UserID((Integer) row.get(F_LEVEL_1_USERID));
            }

            aux.setPassword((String) row.get(F_PASSWORD));
            aux.setRole(roleService.getRoleById(((Long) row.get(F_ROLE)).intValue()));
            payees.add(aux);
        }

        return payees;
    }

    @Override
    public List<Payee> findByRoleAndSP(Integer roleID, int sp_id) {
        String query = SELECT_BY_ROLE;

        if (roleID == 0) {
            query = query.replace(" where role=", "");
        } else {
            query += roleID;
        }

        List        list   = getJdbcTemplate().queryForList(query + " AND Level_1_UserID=?", new Object[] { sp_id });
        List<Payee> payees = new ArrayList<Payee>();
        Map         row    = null;
        Payee       aux    = null;

        for (Object obj : list) {
            aux = new Payee();
            row = (Map) obj;
            aux.setId(((Long) row.get(F_ID)).intValue());
            aux.setEmail((String) row.get(F_EMAIL));
            aux.setAlternateEmail((String) row.get(F_EMAILGUID));
            aux.setEnabled(new Integer(1).equals(row.get(F_ENABLED)));
            aux.setDeleted(new Integer(1).equals(row.get(F_DELETED)));
            aux.setShowGui(new Integer(1).equals(row.get(F_SHOW_GUI)));

            if (row.get(F_LEVEL_1_USERID) != null) {
                aux.setLevel1UserID((Integer) row.get(F_LEVEL_1_USERID));
            }

            aux.setPassword((String) row.get(F_PASSWORD));
            aux.setRole(roleService.getRoleById(((Long) row.get(F_ROLE)).intValue()));
            payees.add(aux);
        }

        return payees;
    }

    public List<Payee> findByRole(int roleId) {
        String query = SELECT_BY_ROLE;

        if (roleId == 0) {
            query = query.replace(" where role=", "");
        } else {
            query += roleId;
        }

        List        list   = getJdbcTemplate().queryForList(query);
        List<Payee> payees = new ArrayList<Payee>();
        Map         row    = null;
        Payee       aux    = null;

        for (Object obj : list) {
            aux = new Payee();
            row = (Map) obj;
            aux.setId(((Long) row.get(F_ID)).intValue());
            aux.setEmail((String) row.get(F_EMAIL));
            aux.setAlternateEmail((String) row.get(F_EMAILGUID));
            aux.setEnabled(new Integer(1).equals(row.get(F_ENABLED)));
            aux.setDeleted(new Integer(1).equals(row.get(F_DELETED)));
            aux.setShowGui(new Integer(1).equals(row.get(F_SHOW_GUI)));

            if (row.get(F_LEVEL_1_USERID) != null) {
                aux.setLevel1UserID((Integer) row.get(F_LEVEL_1_USERID));
            }

            aux.setPassword((String) row.get(F_PASSWORD));
            aux.setRole(roleService.getRoleById(((Long) row.get(F_ROLE)).intValue()));
            payees.add(aux);
        }

        return payees;
    }

    /*
     *  (non-Javadoc)
     * @see springjdbc.example.service.UserService#getByEmail(java.lang.String)
     */
    public Payee getByEmail(String email) {
        RowMapper mapper = new RowMapper() {
            public Payee mapRow(ResultSet rs, int rowNum) throws SQLException {
                Payee payee = new Payee();

                payee.setId(rs.getInt(F_ID));
                payee.setEmail(rs.getString(F_EMAIL));
                payee.setAlternateEmail(rs.getString(F_EMAILGUID));
                payee.setEnabled(rs.getBoolean(F_ENABLED));
                payee.setDeleted(rs.getBoolean(F_DELETED));
                payee.setShowGui(rs.getBoolean(F_SHOW_GUI));
                payee.setLevel1UserID(rs.getInt(F_LEVEL_1_USERID));
                payee.setPassword(rs.getString(F_PASSWORD));
                payee.setRole(roleService.getRoleById(rs.getInt(F_ROLE)));

                return payee;
            }
        };
        final String sql =
            new StringBuilder(SELECT_FROM_USERS).append("where ").append(F_EMAIL).append(" = ?").toString();    // $NON-NLS-1$ //$NON-NLS-2$

        try {
            return (Payee) getJdbcTemplate().queryForObject(sql, new Object[] { email }, mapper);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    /*
     *  (non-Javadoc)
     * @see springjdbc.example.service.UserService#findByEmailGUID(java.lang.String)
     */
    public Payee getByEmailGUID(String emailGUID) {
        RowMapper mapper = new RowMapper() {
            public Payee mapRow(ResultSet rs, int rowNum) throws SQLException {
                Payee payee = new Payee();

                payee.setId(rs.getInt(F_ID));
                payee.setEmail(rs.getString(F_EMAIL));
                payee.setAlternateEmail(rs.getString(F_EMAILGUID));
                payee.setEnabled(rs.getBoolean(F_ENABLED));
                payee.setDeleted(rs.getBoolean(F_DELETED));
                payee.setShowGui(rs.getBoolean(F_SHOW_GUI));
                payee.setLevel1UserID(rs.getInt(F_LEVEL_1_USERID));
                payee.setPassword(rs.getString(F_PASSWORD));
                payee.setRole(roleService.getRoleById(rs.getInt(F_ROLE)));

                return payee;
            }
        };
        final String sql =
            new StringBuilder(SELECT_FROM_USERS).append("where ").append(F_EMAILGUID).append(" = ?").toString();    // $NON-NLS-1$ //$NON-NLS-2$

        try {
            return (Payee) getJdbcTemplate().queryForObject(sql, new Object[] { emailGUID }, mapper);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    /*
     *  (non-Javadoc)
     * @see springjdbc.example.service.UserService#remove(int)
     */
    public void remove(int id) {
        String sql = new StringBuilder(DELETE_FROM_USERS).append("where ").append(F_ID).append(" = ?").toString();    // $NON-NLS-1$//$NON-NLS-2$

        getJdbcTemplate().update(sql, new Object[] { id });
    }

    /*
     *  (non-Javadoc)
     * @see springjdbc.example.service.UserService#save(springjdbc.example.domain.User)
     */
    public void save(Payee payee) {
        String sql = null;

        if (payee.getId() == null) {
            sql = INSERT_INTO_USERS;
            getJdbcTemplate().update(sql, payeeparams(payee));

            int newId = getJdbcTemplate().queryForInt("select max(id) from adm_users");

            getJdbcTemplate().update(
                "insert into adm_user_profiles (firstname, lastname, user_id,efin,businessName) values(?,?,?,'','')",
                new Object[] { payee.getProfile().getFirstName(),
                               payee.getProfile().getLastName(), newId });

            return;
        } else {
            sql = new StringBuilder(UPDATE_USERS).append(F_ID).append("=?").toString();    // $NON-NLS-1$
        }

        getJdbcTemplate().update(sql, payeeparams(payee));
    }

    public void updatePayeeActiveFlag(Payee payee) {
        String sql = null;

        sql = new StringBuilder(UPDATE_PAYEE_ACTIVE_FLAG).append(F_ID).append("=?").toString();
        getJdbcTemplate().update(sql, new Object[] { payee.getEnabled(), payee.getDeleted(), payee.getShowGui(),
                payee.getId() });
    }

    /**
     * @param payee
     * @return
     */
    private Object[] payeeparams(Payee payee) {
        return (payee.getId() == null)
               ? getInsertParams(payee)
               : getUpdateParams(payee);
    }

    private Object[] getInsertParams(Payee payee) {
        Role role = payee.getRole();

        assert(role != null);

        return new Object[] {
            payee.getEmail(), payee.getAlternateEmail(), payee.getPassword(), (payee.getEnabled()
                    ? 1
                    : 0), ((role.getId() == null)
                           ? roleService.getRoleId(role.getName())
                           : role.getId()), payee.getLevel1UserID()
        };
    }

    private Object[] getUpdateParams(Payee payee) {
        Role role = payee.getRole();

        assert(role != null);

        return new Object[] { payee.getEmail(), payee.getAlternateEmail(), (payee.getEnabled()
                ? 1
                : 0), ((role.getId() == null)
                       ? roleService.getRoleId(role.getName())
                       : role.getId()), payee.getId() };
    }

    /*
     *  (non-Javadoc)
     * @see springjdbc.example.service.UserService#deleteEmailGUID(java.lang.String)
     */
    public void deleteEmailGUID(Payee payee) {
        if (getJdbcTemplate().update(DELETE_USERS_EMAILGUID, new Object[] { payee.getId() }) > 0) {
            payee.setAlternateEmail(null);
        }
    }
}



